/*	
	EXEC SP_LOAD_CREDIT_NOTE_LINE_ITEMS 10	
*/
ALTER PROC SP_LOAD_CREDIT_NOTE_LINE_ITEMS @CREDITNOTE_ID INT
AS
BEGIN
	DECLARE @CN_LINEITEMS_CURSOR CURSOR
	DECLARE @CNLT_ID INT
	DECLARE @LINEITEM_ID INT
	DECLARE @COOL_LINE_ITEM_NO INT
	DECLARE @COOL_PO_LINE_ITEM_NO INT
	DECLARE @PART_NUMBER VARCHAR(50)
	DECLARE @COOL_CUSTOMER_PARTNUMBER VARCHAR(50)
	DECLARE @COOL_DESCRIPTION VARCHAR(200)
	DECLARE @SHIPPED_QTY INT
	DECLARE @SHIP_DATE VARCHAR(20)
	DECLARE @COOL_UNIT_COST FLOAT
	DECLARE @SHIP_AMOUNT FLOAT
	DECLARE @REASON VARCHAR(100)
	DECLARE @ADJUSTMENT_QTY INT
	DECLARE @DISCOUNT_PER_UNIT FLOAT
	DECLARE @DISCOUNT_AMOUNT FLOAT
	DECLARE @STATUS VARCHAR(50)
	DECLARE @FIN_HOLD_REASON_CODE VARCHAR(4)
	DECLARE @MKTG_HOLD_REASON_CODE VARCHAR(4)
	DECLARE @OPS_HOLD_REASON_CODE VARCHAR(4)
	DECLARE @SALES_HOLD_REASON_CODE VARCHAR(4)

	DECLARE @RESULTS_TABLE TABLE ( LINEITEM_ID INT, COOL_LINE_ITEM_NO INT, COOL_PO_LINE_ITEM_NO INT, PART_NUMBER VARCHAR(50),
					COOL_CUSTOMER_PARTNUMBER VARCHAR(50), COOL_DESCRIPTION VARCHAR(200), SHIPPED_QTY INT,
					SHIP_DATE VARCHAR(20), COOL_UNIT_COST FLOAT, SHIP_AMOUNT FLOAT, REASON VARCHAR(100),
					ADJUSTMENT_QTY INT, DISCOUNT_PER_UNIT FLOAT, DISCOUNT_AMOUNT FLOAT, STATUS VARCHAR(50), 
					FIN_HOLD_REASON_CODE VARCHAR(4), MKTG_HOLD_REASON_CODE VARCHAR(4), OPS_HOLD_REASON_CODE VARCHAR(4), SALES_HOLD_REASON_CODE VARCHAR(4) )

	SET @CN_LINEITEMS_CURSOR = CURSOR FOR 
				SELECT CNLIT.CNLT_ID, COOL.LINEITEM_ID, COOL.COOL_LINE_ITEM_NO, COOL.COOL_PO_LINE_ITEM_NO, P.PART_NUMBER, COOL.COOL_CUSTOMER_PARTNUMBER, COOL.COOL_DESCRIPTION,
				                    SUM(CSLIT_QTY_SHIPPED) SHIPPED_QTY, CONVERT(VARCHAR,MAX(CST_CONFIRMED_ON), 101) SHIP_DATE, COOL.COOL_UNIT_COST,(SUM(CSLIT_QTY_SHIPPED)*COOL.COOL_UNIT_COST) AS SHIP_AMOUNT,
				                    CNLIT.REASON, CNLIT.ADJUSTMENT_QTY, CNLIT.DISCOUNT_PER_UNIT, CNLIT.DISCOUNT_AMOUNT, CNLIT.STATUS
				                    FROM CREDIT_NOTE_LINE_ITEM_TABLE CNLIT, CUSTOMER_OPEN_ORDER_LINEITEM COOL, 
				                    CUSTOMER_SHIPPING_TABLE CST, CUSTOMER_SHIPPING_LINE_ITEM_TABLE CSLIT, PRODUCT P, CREDIT_NOTE_TABLE CNT
				                    WHERE COOL.LINEITEM_ID = CNLIT.LINEITEM_ID
				                    AND CST.SALESORDER_ID = COOL.SALESORDER_ID
				                    AND COOL.LINEITEM_ID=CSLIT.LINEITEM_ID
				                    AND  CSLIT.CUSTOMERSHIPPING_ID = CST.CUSTOMERSHIPPING_ID
				                    AND CST.CST_STATUS = 'SHIPPED'
				                    AND P.PRODUCT_ID = COOL.PRODUCT_ID
				                    AND CNLIT.CN_ID = CNT.CREDITNOTE_ID
						    AND CNT.CREDITNOTE_ID = @CREDITNOTE_ID 
						    AND CST.CUSTOMERSHIPPING_ID = CNT.CUSTOMERSHIPPING_ID
				                    GROUP BY CNLIT.CNLT_ID, COOL.LINEITEM_ID,COOL.COOL_LINE_ITEM_NO, COOL_PO_LINE_ITEM_NO, P.PART_NUMBER, COOL.COOL_CUSTOMER_PARTNUMBER, COOL.COOL_DESCRIPTION,COOL.COOL_UNIT_COST, CNLIT.REASON, CNLIT.ADJUSTMENT_QTY, CNLIT.DISCOUNT_PER_UNIT, CNLIT.DISCOUNT_AMOUNT, CNLIT.STATUS
				                    ORDER BY COOL.COOL_LINE_ITEM_NO
	

	OPEN @CN_LINEITEMS_CURSOR
	FETCH NEXT FROM @CN_LINEITEMS_CURSOR INTO @CNLT_ID, @LINEITEM_ID, @COOL_LINE_ITEM_NO, @COOL_PO_LINE_ITEM_NO, @PART_NUMBER, @COOL_CUSTOMER_PARTNUMBER , @COOL_DESCRIPTION, @SHIPPED_QTY, @SHIP_DATE , @COOL_UNIT_COST , @SHIP_AMOUNT, @REASON , @ADJUSTMENT_QTY, @DISCOUNT_PER_UNIT, @DISCOUNT_AMOUNT, @STATUS 
	WHILE @@FETCH_STATUS = 0
	 BEGIN
		--GETTING CREDIT/DEBIT NOTE HOLD STATUS FOR THE LINE ITEM

		SELECT @FIN_HOLD_REASON_CODE = HOLD_REASON_CODE FROM CREDIT_NOTE_HOLDS_TABLE WHERE HOLD_BY_GROUP = 'FINANCE' AND CNLT_ID = @CNLT_ID
		SELECT @MKTG_HOLD_REASON_CODE = HOLD_REASON_CODE FROM CREDIT_NOTE_HOLDS_TABLE WHERE HOLD_BY_GROUP = 'MARKETING' AND CNLT_ID = @CNLT_ID
		SELECT @OPS_HOLD_REASON_CODE = HOLD_REASON_CODE FROM CREDIT_NOTE_HOLDS_TABLE WHERE HOLD_BY_GROUP = 'OPERATIONS' AND CNLT_ID = @CNLT_ID
		SELECT @SALES_HOLD_REASON_CODE = HOLD_REASON_CODE FROM CREDIT_NOTE_HOLDS_TABLE WHERE HOLD_BY_GROUP = 'SALES' AND CNLT_ID = @CNLT_ID
		
		INSERT INTO @RESULTS_TABLE VALUES ( @LINEITEM_ID, @COOL_LINE_ITEM_NO, @COOL_PO_LINE_ITEM_NO, @PART_NUMBER, @COOL_CUSTOMER_PARTNUMBER , @COOL_DESCRIPTION, @SHIPPED_QTY, @SHIP_DATE , @COOL_UNIT_COST , @SHIP_AMOUNT, @REASON , @ADJUSTMENT_QTY, @DISCOUNT_PER_UNIT, @DISCOUNT_AMOUNT, @STATUS, @FIN_HOLD_REASON_CODE, @MKTG_HOLD_REASON_CODE, @OPS_HOLD_REASON_CODE, @SALES_HOLD_REASON_CODE )
		
		SET @CNLT_ID = NULL
		SET @LINEITEM_ID = NULL
		SET @COOL_LINE_ITEM_NO = NULL
		SET @COOL_PO_LINE_ITEM_NO = NULL
		SET @PART_NUMBER = NULL
		SET @COOL_CUSTOMER_PARTNUMBER = NULL
		SET @COOL_DESCRIPTION = NULL
		SET @SHIPPED_QTY = NULL
		SET @SHIP_DATE = NULL
		SET @COOL_UNIT_COST = NULL
		SET @SHIP_AMOUNT = NULL
		SET @REASON = NULL
		SET @ADJUSTMENT_QTY = NULL
		SET @DISCOUNT_PER_UNIT = NULL
		SET @DISCOUNT_AMOUNT = NULL
		SET @STATUS = NULL
		SET @FIN_HOLD_REASON_CODE = NULL
		SET @MKTG_HOLD_REASON_CODE = NULL
		SET @OPS_HOLD_REASON_CODE = NULL
		SET @SALES_HOLD_REASON_CODE = NULL
		
		FETCH NEXT FROM @CN_LINEITEMS_CURSOR INTO @CNLT_ID, @LINEITEM_ID, @COOL_LINE_ITEM_NO, @COOL_PO_LINE_ITEM_NO, @PART_NUMBER, @COOL_CUSTOMER_PARTNUMBER , @COOL_DESCRIPTION, @SHIPPED_QTY, @SHIP_DATE , @COOL_UNIT_COST , @SHIP_AMOUNT, @REASON , @ADJUSTMENT_QTY, @DISCOUNT_PER_UNIT, @DISCOUNT_AMOUNT, @STATUS 
	 END

	SELECT * FROM @RESULTS_TABLE ORDER BY PART_NUMBER
END